package com.thoughtworks.biblioteca;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//Understands a movie database containing the movie details
public class MovieDB {
	Connection connectionToBibliotecaDatabase = null;

	public MovieDB() {
		connectionToBibliotecaDatabase = MySQLConnection.obtainConnection();
	}

	public List<Movie> getMovies() {
		List<Movie> moviesList = new ArrayList<Movie>();

		try {
			Statement selectMoviesFromDatabase = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet moviesRecordSet = selectMoviesFromDatabase
					.executeQuery("SELECT * FROM movies");
			moviesRecordSet.first();
			do {
				moviesList.add(new Movie(moviesRecordSet.getString("movie"),
						moviesRecordSet.getString("director"), moviesRecordSet
								.getString("rating")));
			} while (moviesRecordSet.next());
		} catch (Exception e) {
		}
		return moviesList;

	}

	public Movie getMovie(String movieName) {
		try {
			Statement selectMoviesFromDatabase = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet moviesRecordSet = selectMoviesFromDatabase
					.executeQuery("SELECT * FROM movies");
			moviesRecordSet.first();
			do {
				if (movieName.equals(moviesRecordSet.getString("movie"))) {
					return new Movie(movieName, moviesRecordSet
							.getString("director"), moviesRecordSet
							.getString("rating"));
				}
			} while (moviesRecordSet.next());
		} catch (Exception e) {
		}
		return null;
	}

	public String movieUserRating(String userName, String movieName) {
		try {
			Statement selectMoviesUserRating = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet userRatingsRecordSet = selectMoviesUserRating
					.executeQuery("SELECT * FROM user_ratings");
			userRatingsRecordSet.first();
			do {
				if (userName.equals(userRatingsRecordSet.getString("username"))) {
					if (movieName.equals(userRatingsRecordSet
							.getString("movie"))) {
						return userRatingsRecordSet.getString("rating");
					}
				}
			} while (userRatingsRecordSet.next());
		} catch (Exception e) {
		}
		return null;

	}

	public void updateRating(String userName, String movieName, int userRating) {
		if (movieUserRating(userName, movieName) != null) {
			try {
				PreparedStatement preparedRatingsUpdateStatement;
				String updateUserRating = "update user_ratings set rating = ? where username=? and movie=?;";
				preparedRatingsUpdateStatement = connectionToBibliotecaDatabase
						.prepareStatement(updateUserRating);
				preparedRatingsUpdateStatement.setString(2, userName);
				preparedRatingsUpdateStatement.setString(3, movieName);
				preparedRatingsUpdateStatement.setInt(1, userRating);
				preparedRatingsUpdateStatement.executeUpdate();
			} catch (Exception e) {
			}

		} else {
			try {
				PreparedStatement preparedInsertRatingsStatement;
				String insertNewUserRating = "insert into user_ratings values (?,?,?)";
				preparedInsertRatingsStatement = connectionToBibliotecaDatabase
						.prepareStatement(insertNewUserRating);
				preparedInsertRatingsStatement.setString(1, userName);
				preparedInsertRatingsStatement.setString(2, movieName);
				preparedInsertRatingsStatement.setInt(3, userRating);
				preparedInsertRatingsStatement.executeUpdate();
			} catch (Exception e) {
			}
		}
		updateAverageUserRating(movieName);

	}

	private void updateAverageUserRating(String movieName) {
		int totalRating = 0;
		int averageRating = 0;
		int numberOfRatings = 0;
		try {
			Statement selectMoviesUserRating = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet userRatingsRecordSet = selectMoviesUserRating
					.executeQuery("SELECT * FROM user_ratings");
			userRatingsRecordSet.first();
			do {
				if (movieName.equals(userRatingsRecordSet.getString("movie"))) {
					numberOfRatings++;
					totalRating += Integer.parseInt(userRatingsRecordSet
							.getString("rating"));
				}
			} while (userRatingsRecordSet.next());
			averageRating = totalRating / numberOfRatings;
			String averageRatingString = new String(Integer
					.toString(averageRating));
			PreparedStatement preparedRatingsUpdateStatement;
			String updateAverageUserRating = "update movies set rating=? where movie=?";
			preparedRatingsUpdateStatement = connectionToBibliotecaDatabase
					.prepareStatement(updateAverageUserRating);
			preparedRatingsUpdateStatement.setString(1, averageRatingString);
			preparedRatingsUpdateStatement.setString(2, movieName);
			preparedRatingsUpdateStatement.executeUpdate();
		} catch (Exception e) {

		}

	}

	public void removeUserRating(String userName, String movieName) {
		try {
			PreparedStatement preparedRatingsDeleteStatement;
			String deleteUserRating = "delete from user_ratings where username=? and movie=?";
			preparedRatingsDeleteStatement = connectionToBibliotecaDatabase
					.prepareStatement(deleteUserRating);
			preparedRatingsDeleteStatement.setString(1, userName);
			preparedRatingsDeleteStatement.setString(2, movieName);
			preparedRatingsDeleteStatement.executeUpdate();
		} catch (Exception e) {
		}
	}

	public void addMovie(Movie newMovie) {
		try {
			PreparedStatement preparedInsertRatingsStatement;
			String insertNewUserRating = "insert into movies values (?,?,?)";
			preparedInsertRatingsStatement = connectionToBibliotecaDatabase
					.prepareStatement(insertNewUserRating);
			preparedInsertRatingsStatement
					.setString(1, newMovie.getMovieName());
			preparedInsertRatingsStatement.setString(2, newMovie
					.getMovieDirector());
			preparedInsertRatingsStatement.setString(3, newMovie
					.getMovieRating());
			preparedInsertRatingsStatement.executeUpdate();
		} catch (Exception e) {

		}

	}

	public void removeMovie(Movie newMovie) {
		try {
			PreparedStatement preparedRatingsDeleteStatement;
			String deleteUserRating = "delete from movies where movie=?";
			preparedRatingsDeleteStatement = connectionToBibliotecaDatabase
					.prepareStatement(deleteUserRating);
			preparedRatingsDeleteStatement
					.setString(1, newMovie.getMovieName());
			preparedRatingsDeleteStatement.executeUpdate();
		} catch (Exception e) {
		}

	}

	public void addRequest(String movieName, String loggedUser) {
		try {
			PreparedStatement preparedInsertRequestsStatement;
			String insertNewUserRequest = "insert into user_requests(username,movie) values (?,?)";
			preparedInsertRequestsStatement = connectionToBibliotecaDatabase
					.prepareStatement(insertNewUserRequest);
			preparedInsertRequestsStatement.setString(1, loggedUser);
			preparedInsertRequestsStatement.setString(2, movieName);
			preparedInsertRequestsStatement.executeUpdate();
		} catch (Exception e) {

		}

	}

	public List<MovieRequest> getRequests(String userName) {
		List<MovieRequest> moviesRequestList = new ArrayList<MovieRequest>();

		try {
			Statement selectMoviesRequestFromDatabase = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet moviesRequestRecordSet = selectMoviesRequestFromDatabase
					.executeQuery("SELECT * FROM user_requests where username=\""
							+ userName + "\"");
			moviesRequestRecordSet.first();
			do {
				moviesRequestList.add(new MovieRequest(moviesRequestRecordSet
						.getInt("id"), moviesRequestRecordSet
						.getString("username"), moviesRequestRecordSet
						.getString("movie"), moviesRequestRecordSet
						.getString("status")));
			} while (moviesRequestRecordSet.next());
		} catch (Exception e) {
		}
		return moviesRequestList;
	}

	public void removeRequest(String movie,String username) {
		try {
			PreparedStatement preparedRequestDeleteStatement;
			String deleteUserRequest = "delete from user_requests where username=? and movie=?";
			preparedRequestDeleteStatement = connectionToBibliotecaDatabase
					.prepareStatement(deleteUserRequest);
			preparedRequestDeleteStatement.setString(1, username);
			preparedRequestDeleteStatement.setString(2, movie);
			preparedRequestDeleteStatement.executeUpdate();
		} catch (Exception e) {
		}

	}

	public List<MovieRequest> getAllRequests() {
		List<MovieRequest> moviesRequestList = new ArrayList<MovieRequest>();
		try {
			Statement selectMoviesRequestFromDatabase = connectionToBibliotecaDatabase
					.createStatement();
			ResultSet moviesRequestRecordSet = selectMoviesRequestFromDatabase
					.executeQuery("SELECT * FROM user_requests");
			moviesRequestRecordSet.first();
			do {
				moviesRequestList.add(new MovieRequest(moviesRequestRecordSet
						.getInt("id"), moviesRequestRecordSet
						.getString("username"), moviesRequestRecordSet
						.getString("movie"), moviesRequestRecordSet
						.getString("status")));
			} while (moviesRequestRecordSet.next());
		} catch (Exception e) {
		}
		return moviesRequestList;
	}

	public void closeRequest(int requestID) {
		try {
			PreparedStatement preparedInsertRequestsStatement;
			String insertNewUserRequest = "update user_requests set status=\"Closed\" where id=?";
			preparedInsertRequestsStatement = connectionToBibliotecaDatabase
					.prepareStatement(insertNewUserRequest);
			preparedInsertRequestsStatement.setInt(1, requestID);
			preparedInsertRequestsStatement.executeUpdate();
		} catch (Exception e) {

		}
		
		
	}
}
